#!/bin/sh
#
# OracleASMDBRemount.sh: shell script to automate cloning and bring up an Oracle database using backup image copies
#
# usage: OracleASMDBRemount.sh <New database sid name> <Oracle Home path> <diskgroup name>"

set +x
nsid=$1
oracleHome=$2
dg=$3
osouser=$4

dataDir="+"$dg

scriptsRoot=${LB_ROOT}
if [ -z "$scriptsRoot" ];then
    echo "missing env LB_ROOT"
    exit 1
fi
   
. ${scriptsRoot}/oracle/oracleclone/OracleAppMountComFunc.sh

BASEDIR=$(dirname $0)
echo $BASEDIR
cd $BASEDIR
# correctly set the ORACLE_BASE AND ORACLE_HOME  for your environment
export ORACLE_HOME=$oracleHome
export PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH

if [ ! -d $ORACLE_HOME ]; then
   echo "could not find ORACLE_HOME "
   exit 1
fi

sqlplus=$ORACLE_HOME/bin/sqlplus

#setup process to get required info from mounted diskgroup
oratab=/etc/oratab
gridhome=`get_grid_home $oratab`
if [ $? -ne 0 ]; then
   echo "failed to get ORACLE_HOME for grid"
   exit 1
fi

if [ ! -d "$gridhome" ]; then
   echo "ORACLE_HOME $gridhome for grid does not exists"
   exit 1
fi

guser=`get_asm_user`
if [ $? -ne 0 ]; then
   echo "failed to get grid user"
   exit 1
fi

asmsid=`grep -i +asm $oratab | cut -d":" -f1`
export ORACLE_HOME=$gridhome
export ORACLE_SID=$asmsid
export PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH

#get generation
gen=`get_apm_latest_gen $dg $guser`
psid=`get_first_apm_sid $dg $guser $gen`
if [ $? -ne 0 ]; then
   echo "error locating first mount sid: $psid"
   exit 1
fi
#create datafile dir for remount instance
su $guser -c $asmcmd << EOF
cd $dg
mkdir $nsid
cd $nsid
mkdir datafile
EOF
#

#check psid exists
spfile=`get_spfile $dg $psid $guser`
pspfile="+"$dg"/"$psid"/"$spfile
spname=`echo $spfile | cut -d"." -f1`
tpsid=`echo $spname | cut -c7-`
nspfile="+"$dg"/"$psid"/spfile"$nsid".ora"
#remove old spfile
#su  $guser -c "asmcmd rm $pspfile"
pcontrolfile=`get_controlfile $dg $psid $guser`

export ORACLE_HOME=$oracleHome
export ORACLE_SID=$tpsid
export PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH

pfile=$ORACLE_HOME"/actpfile_"$nsid".ora"
pfile_hold=$ORACLE_HOME"/actpfile_"$nsid".ora_hold"

#create parameter file under ORACLE_HOME/dbs
odefaultpfile="$ORACLE_HOME/dbs/init"$tpsid".ora"
if [ ! -e "$odefaultpfile" ]; then
   echo "SPFILE='$pspfile'" > $odefaultpfile
fi

change_own $osouser $odefaultpfile
change_own $osouser $pfile

su  $osouser -c "sqlplus -s -L / as sysdba " << EOF
set echo off
set feed off
create pfile='$pfile' from spfile='$pspfile';
exit;
EOF


lastdg=`get_last_dgname $pfile`

samediskgroup="true"
if [ "$lastdg" = "$dg" ]; then
    echo "using same diskgroup"
else
    echo "using different diskgroup"
    samediskgroup="false"
fi

tpsidc=`echo $tpsid | tr '[A-Z]' '[a-z]'`
tpsidu=`echo $tpsid | tr '[a-z]' '[A-Z]'`
if [ "$tpsidc" = "$nsid" ] || [ "$tpsidu" = "$nsid" ]; then
    echo "remount with same SID"
    if [ "$samediskgroup" = "true" ]; then
       echo "remount with same SID $nsid using same diskgroup"
       # copy pfile to ORACLE_HOME/dbs, start database
       #cp $initfile $ORACLE_HOME/dbs/
ORACLE_SID=$nsid
export ORACLE_SID

smpfile=$ORACLE_HOME"/dbs/init"$ORACLE_SID".ora"
smspfile="SPFILE='"$dataDir"/"$nsid"/spfile"$nsid".ora'"
su  $osouser -c "echo $smspfile > $smpfile"

change_own $osouser $smpfile

su  $osouser -c "sqlplus / as sysdba " << EOF1
shutdown immediate;
startup mount;
alter database open;
shutdown immediate;
exit;
EOF1


else
       echo "remount with same SID $nsid using different diskgroup"
       # same SID, different mp, change dataDir in pfile
       #sed -i "s/$osid/$nsid/g" $initfile
       
       sed "s=${lastdg}=${dg}=g" $pfile > $pfile_hold
       mv $pfile_hold $pfile

change_own $osouser $pfile

export ORACLE_SID=$tpsid
smpfile=$ORACLE_HOME"/dbs/init"$ORACLE_SID".ora"
smspfile="SPFILE='"$dataDir"/"$tpsid"/spfile"$nsid".ora'"
su  $osouser -c "echo $smspfile > $smpfile"

su  $osouser -c "$sqlplus / as sysdba " << EOF2
startup nomount force pfile='$pfile';
alter database mount;
create spfile='$pspfile' from pfile='$pfile';
shutdown immediate;
exit;
EOF2


initfilex="$ORACLE_HOME/dbs/init"$tpsid".ora"
initfilex_hold="$ORACLE_HOME/dbs/init"$tpsid".ora_hold"
sed "s=${lastdg}=${dg}=g" $initfilex > $initfilex_hold
mv $initfilex_hold $initfilex

change_own $osouser $initfiley

su  $osouser -c "$sqlplus / as sysdba " << EOF3
startup mount;
@${scriptsRoot}/oracle/asmclone/renameDatafile.sql $dataDir $psid;
@${scriptsRoot}/oracle/asmclone/renamedf_generated.sql
alter database disable block change tracking;
alter database open;
exit;
EOF3

    fi
else
    # Different SID for remount
    echo "remount with different SID"
    if [ "$samediskgroup" = "true" ]; then
       echo "remount with SID $nsid to replace $psid using same diskgroup"
       #cp $initfile $ORACLE_HOME/dbs/
export ORACLE_SID=$tpsid
smpfile=$ORACLE_HOME"/dbs/init"$ORACLE_SID".ora"
smspfile="SPFILE='"$dataDir"/"$tpsid"/spfile"$tpsid".ora'"
su  $osouser -c "echo $smspfile > $smpfile"

#export ORACLE_SID=$tpsid
su  $osouser -c "$sqlplus / as sysdba " << EOF4
startup mount;
alter system set cluster_database=FALSE scope=spfile;
alter database open;
exit;
EOF4

roTBSList=`su $osouser -c "sqlplus -S / as sysdba " << 'EOF1'
@${scriptsRoot}/oracle/sqlplusShell/readonlyDatafileCatalog.sql
exit;
EOF1`

if [ ! -z "$roTBSList" ]; then
   roTBS=''
   for i in ${roTBSList}; do
      echo "$i"
      if [ -z "$roTBS" ]; then
          roTBS="'"$i"'"
      else
          roTBS=$roTBS",'"$i"'"
      fi
   done
else
   roTBS="'NONE'"
fi

echo "$roTBS"
su $osouser -c "$sqlplus / as sysdba " << EOF
@nidROTBS.sql "$roTBS" ONLINE
@nidROTBS.sql "$roTBS" "READ WRITE"
shutdown immediate;
startup mount;
exit;
EOF

set +x
username="sys"
pssword="change_on_install"
if [ ! -z "$ouser" ]; then
   username=$ouser
fi
if [ ! -z "$opass" ]; then
   pssword=$opass
fi

su  $osouser -c "$ORACLE_HOME/bin/nid TARGET=$username/$pssword DBNAME=$nsid SETNAME=yes LOGFILE=newsidlog.log"

        if [ $? != 0 ]; then
          echo "failed to rename database to $nsid"
          exit 1
        fi

pfile_tmp=$ORACLE_HOME"/actpfile_tmp.ora"
pfile_tmp_hold=$ORACLE_HOME"/actpfile_tmp.ora_hold"
#process pfile with new name before mount
ctrfile_entry=`grep control_files $pfile`
su  $osouser -c "grep -v control_files $pfile > $pfile_tmp"
sed "s/$tpsid/$nsid/g" $pfile_tmp > $pfile_tmp_hold
mv $pfile_tmp_hold $pfile_tmp
#sed "s=${tpsid}=${nsid}=g" $pfile_tmp > $pfile_tmp_hold
#mv $pfile_tmp_hold $pfile_tmp
#new controlfile is written after dbname change, get this new name and update initfile
export ORACLE_HOME=$gridhome
export ORACLE_SID=$asmsid
export PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH
ncontrolfile=`get_controlfile $dg $psid $guser`
echo "$ctrfile_entry" >> $pfile_tmp
sed "s=${pcontrolfile}=${ncontrolfile}=g" $pfile_tmp > $pfile_tmp_hold
mv $pfile_tmp_hold $pfile_tmp
mv $pfile_tmp $pfile

change_own $osouser $pfile

export ORACLE_HOME=$oracleHome
export PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH
initfilex="$ORACLE_HOME/dbs/init"$tpsid".ora"
initfilex_hold="$ORACLE_HOME/dbs/init"$tpsid".ora_hold"
pspora="spfile"$tpsid".ora"
nspora="spfile"$nsid".ora"
sed "s=${pspora}=${nspora}=g" $initfilex > $initfilex_hold
mv $initfilex_hold $initfilex
initfiley="$ORACLE_HOME/dbs/init"$nsid".ora"
mv $initfilex $initfiley 

change_own $osouser $initfiley

export ORACLE_SID=$nsid
su  $osouser -c "$sqlplus / as sysdba " << EOF5
startup mount pfile='$pfile';
create spfile='$nspfile' from pfile='$pfile';
alter database open;
@nidROTBS.sql "$roTBS" "READ ONLY"
shutdown immediate;
exit;
EOF5

export ORACLE_HOME=$gridhome
export ORACLE_SID=$asmsid
export PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH
#remove old spfile
su  $guser -c "asmcmd rm $pspfile"

    else
         echo "remount with SID $nsid to replace $psid using different diskgroup"
         sed "s=${lastdg}=${dg}=g" $pfile > $pfile_hold
         mv $pfile_hold $pfile

change_own $osouser $pfile

export ORACLE_SID=$tpsid
export ORACLE_HOME=$oracleHome
export PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH

smpfile=$ORACLE_HOME"/dbs/init"$ORACLE_SID".ora"
smspfile="SPFILE='"$dataDir"/"$tpsid"/spfile"$tpsid".ora'"
su  $osouser -c "echo $smspfile > $smpfile"

su  $osouser -c "$sqlplus / as sysdba " << EOF6
startup nomount force pfile='$pfile';
create spfile='$pspfile' from pfile='$pfile';
shutdown immediate;
exit;
EOF6


initfilex="$ORACLE_HOME/dbs/init"$tpsid".ora"
initfilex_hold="$ORACLE_HOME/dbs/init"$tpsid".ora_hold"
sed "s=${lastdg}=${dg}=g" $initfilex > $initfilex_hold
mv $initfilex_hold $initfilex

change_own $osouser $initfilex

su  $osouser -c "$sqlplus / as sysdba " << EOF7
startup mount;
@${scriptsRoot}/oracle/asmclone/renameDatafile.sql $dataDir $tpsid;
@${scriptsRoot}/oracle/asmclone/renamedf_generated.sql
alter database disable block change tracking;
shutdown immediate;
exit;
EOF7


export ORACLE_SID=$tpsid
su  $osouser -c "$sqlplus / as sysdba " << EOF8
startup mount;
alter system set cluster_database=FALSE scope=spfile;
alter database open;
exit;
EOF8

#reset ORACLE_HOME to Oracle
export ORACLE_HOME=$oracleHome
export PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH

set +x
username="sys"
pssword="change_on_install"
if [ ! -z "$ouser" ]; then
   username=$ouser
fi
if [ ! -z "$opass" ]; then
   pssword=$opass
fi

roTBSList=`su $osouser -c "sqlplus -S / as sysdba " << 'EOF1'
@${scriptsRoot}/oracle/sqlplusShell/readonlyDatafileCatalog.sql
exit;
EOF1`

if [ ! -z "$roTBSList" ]; then
   roTBS=''
   for i in ${roTBSList}; do
      echo "$i"
      if [ -z "$roTBS" ]; then
          roTBS="'"$i"'"
      else
          roTBS=$roTBS",'"$i"'"
      fi
   done
else
   roTBS="'NONE'"
fi

echo "$roTBS"
su $osouser -c "$sqlplus / as sysdba " << EOF
@nidROTBS.sql "$roTBS" ONLINE
@nidROTBS.sql "$roTBS" "READ WRITE"
shutdown immediate;
startup mount;
exit;
EOF

su  $osouser -c "$ORACLE_HOME/bin/nid TARGET=$username/$pssword DBNAME=$nsid SETNAME=yes LOGFILE=newsidlog.log"
        if [ $? != 0 ]; then
          echo "failed to rename database to $nsid after changing datafile path"
          exit 1
        fi

pfile_tmp=$ORACLE_HOME"/actpfile_tmp.ora"
pfile_tmp_hold=$ORACLE_HOME"/actpfile_tmp.ora_hold"
#process pfile with new name before mount
ctrfile_entry=`grep control_files $pfile`
su  $osouser -c "grep -v control_files $pfile > $pfile_tmp"
sed "s/$tpsid/$nsid/g" $pfile_tmp > $pfile_tmp_hold
mv $pfile_tmp_hold $pfile_tmp
#sed "s=${tpsid}=${nsid}=g" $pfile_tmp > $pfile_tmp_hold
#mv $pfile_tmp_hold $pfile_tmp
#new controlfile is written after dbname change, get this new name and update initfile
export ORACLE_HOME=$gridhome
export ORACLE_SID=$asmsid
export PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH
ncontrolfile=`get_controlfile $dg $psid $guser`
echo "$ctrfile_entry" >> $pfile_tmp
sed "s=${pcontrolfile}=${ncontrolfile}=g" $pfile_tmp > $pfile_tmp_hold
mv $pfile_tmp_hold $pfile_tmp
mv $pfile_tmp $pfile

change_own $osouser $pfile

export ORACLE_HOME=$oracleHome
export PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH
initfilex="$ORACLE_HOME/dbs/init"$tpsid".ora"
initfilex_hold="$ORACLE_HOME/dbs/init"$tpsid".ora_hold"
pspora="spfile"$tpsid".ora"
nspora="spfile"$nsid".ora"
sed "s=${pspora}=${nspora}=g" $initfilex > $initfilex_hold
mv $initfilex_hold $initfilex
initfiley="$ORACLE_HOME/dbs/init"$nsid".ora"
mv $initfilex $initfiley

change_own $osouser $initfiley

export ORACLE_SID=$nsid
su  $osouser -c "$sqlplus / as sysdba " << EOF9
startup mount pfile='$pfile';
create spfile='$nspfile' from pfile='$pfile';
alter database open;
@nidROTBS.sql "$roTBS" "READ ONLY"
shutdown immediate;
exit;
EOF9


export ORACLE_HOME=$gridhome
export ORACLE_SID=$asmsid
export PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH
#remove old spfile
su  $guser -c "asmcmd rm $pspfile"

    fi
    
fi

#reset ORACLE_HOME to Oracle
export ORACLE_HOME=$oracleHome
export PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH
export ORACLE_SID=$nsid

orapw1=$ORACLE_HOME"/dbs/orapw"$nsid

if [ -e $orapw1 ]; then
   rm -f $orapw1
fi

set +x
syspwd="change_on_install"
if [ ! -z "$ouser" ] && [ "$ouser" = "sys" ] && [ ! -z "$opass" ]; then
   syspwd=$opass
fi

su $osouser -c "$ORACLE_HOME/bin/orapwd file=$orapw1 password=$syspwd entries=5"

#grant user sysdba privilege
if [ ! -z $ouser ] && [ "$ouser" != "sys" ]; then
export ORACLE_SID=$nsid
export ORACLE_HOME=$oracleHome
export PATH=$PATH:$ORACLE_HOME/bin
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH
su $osouser -c "$sqlplus / as sysdba " << EOF
startup;
grant sysdba to $ouser;
shutdown immediate;
exit;
EOF

fi

su  $osouser -c "sqlplus / as sysdba " << EOF0
startup;
exit;
EOF0



if [ $? -ne 0 ]; then
   echo "remount of clone database to $nsid failed"
   exit 1
fi

#reconfig controlfile snapshot location
export ORACLE_SID=$nsid
export ORACLE_HOME=$oracleHome
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH
dbrecover="dbrecover_"$nsid".txt"

vnum=0
vnum=`su $osouser -c "sqlplus -S / as sysdba " << 'EOF1'
set head off;
set feedback off
select count(*) from v\$instance where version like '12%';
exit;
EOF1`
echo "********************* vnum=$vnum ***********"
pdbcnt=0
if [ "$vnum" -gt "0" ]; then
  pdbcnt=`su $osouser -c "sqlplus -S / as sysdba " << 'EOF1'
  set head off;
  set feedback off
  select count(*) from v\$pdbs where name like 'PDB%SEED';
  exit;
EOF1`
fi
export vnum
export pdbcnt

if [ "$pdbcnt" -gt "0" ]; then
su $osouser -c "$ORACLE_HOME/bin/rman target / log=$dbrecover append " << EOF
run {
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '$dataDir/$nsid/snapcf_$nsid.f';
alter session set "_oracle_script"=true;
ALTER pluggable database all open;
alter pluggable database all save state;
}
exit;
EOF
else
su $osouser -c "$ORACLE_HOME/bin/rman target / log=$dbrecover append " << EOF
run {
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '$dataDir/$nsid/snapcf_$nsid.f';
}
exit;
EOF
fi

otab=$nsid":"$ORACLE_HOME":Y"
echo "$otab" >> /etc/oratab

exit 0
